# -*- coding: utf-8 -*-
from sqlalchemy import and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session, Query, aliased

from py_engine import engine, session
import logging

logger = logging.getLogger(__name__)

Base = declarative_base()
Base.metadata.reflect(bind=engine)
tables = Base.metadata.tables


class TableName(Base):
    __table__ = tables['table_name']
    pass


class TableJoin(Base):
    __table__ = tables['table_join']
    pass


def test_table_query_join():
    logger.info(f'start')
    query: Query = session.query(
        TableJoin.column_name,
    )
    # 表结构没有外键配置，表连接需要关联关系
    query = query.select_from(
        TableName
    ).join(
        TableJoin,
        # join中多条件必须使用and_
        and_(
            TableJoin.primary_key == TableName.primary_key,
            TableJoin.primary_key.in_([1, 2, ]),
        ),

    ).join(  # 可以多次join关联
        TableJoin,
        TableJoin.foreign_key == TableName.primary_key,
        # ).filter(
        #     TableJoin.primary_key.in_([1, 2, ])
    ).filter_by(unique_index='648684d2e4e342d29d2fd05a2f076054')

    count = query.count()
    logger.info(f'count:{count}')
    result = query.all()

    pass


def test_outer_join():
    session.query(TableName).outerjoin(
        TableJoin,
        TableJoin.foreign_key == TableName.primary_key,
    )


if __name__ == '__main__':
    # test_join()

    test_table_query_join()
    pass
